'''
匯入pymysql模塊 將使用pymysql.connect(),commit().cursor().execute(),fetchall()
	pymysql.connect()
	pymysql.connect().commit()
	pymysql.connect().rollback()
	pymysql.connect().cursor()
	pymysql.connect().cursor().execute()
	pymysql.connect().cursor().fetchall()
從module包匯入config.py 用來存放伺服器地址及使用者名稱密碼 etc..	
匯入re模塊 用來比對用戶交易紀錄表是否已經存在
建立class sql_connect() 用來連接及操縱資料庫，達成對表格執行增刪改查命令。
	__init__() 用於初始化連接
	__del__() 用於離開時關閉資料庫
	create_table() 用於創建新表格	*sql語句不組裝
	drop() 用於刪除表格		*sql語句不組裝
	alter() 用於修改表格欄位  *alter(self,table_name,action,the_rest):
	insert() 用於插入數據	*insert(self,table_name,tuple1,tuple2,m = False):
	delete() 用於刪除數據	*delete(self,table_name,condition_)
	update() 用於修改數據	*update(self,table_name,set_field,set_value,condition_)
	select() 用於查找數據	*sql語句不組裝
	exe() 用於執行self.sql內的sql語句	*exe(self,sel=False,alt=False)
	table_exist() 比對資料庫中是否已存在相同資料表	*table_exist(self,table_name)
'''


import pymysql   #import third party module 
from module import config
import re


'''connection class'''
class Sql_connect:
	''' __init__'''
	def __init__(self):

		#connect to specified host 
		self.db = pymysql.connect(host=config.HOST,user=config.USER,password=config.PWD,db=config.DB,charset=config.CHARSET)

		#get an object cursor
		self.cursor = self.db.cursor()

		#to storage whatever be fetching
		self.data = []

		#to storage sql statement
		self.sql = ""

		#within try and except to avoid causing-stop error
		try: 
			self.cursor.execute("create database if not exists mydb2")
			self.cursor.execute("use mydb2")
			
			self.cursor.execute("select user()")  #some descripions for acknowledging successful access 
			self.data.append(self.cursor.fetchone())

			self.cursor.execute("select database()") #some descripions for acknowledging successful access 
			self.data.append(self.cursor.fetchone())
			print("\n\nHello " +str(self.data[0][0]) +"\n\nYou are now using database : "+ str(self.data[1][0])+"\n\n\n")	
			del self.data  #option

		except Exception as err: 
			print("error occured :" + str(err) )  #some descriptions for acknowledging failed access

	'''__del__'''
	def __del__(self):
		
		#close database 
		self.db.close()

	'''create table'''
	def create(self):

		#put SQL statement directly at attribute, self.sql, and call exe() would be the most convenient way.
		if self.exe():
			print("the table has been successfully created!")

	'''drop table'''
	def drop(self):

		#put SQL statement directly at attribute, self.sql, and call exe() would be the most convenient way.
		self.sql = self.sql.lower()
		self.sql = self.sql.replace("drop table","drop table if exists")
		yn = input("Do you really want to drop table "+self.sql[21:]+" ? (Y/N)")
		if yn.lower() == "y":
			if self.exe():
				print("the table has been successfully dropped!")

	'''alter table'''
	def alter(self,table_name,action,the_rest):
		
		#action are either add, drop, change, charset= , engine= 
		self.sql = "alter table "+table_name+" "+action+" "+the_rest
		if self.exe(alt = True):
			print("the table has been successfully alternated!")

	'''insert thread(s)'''
	def insert(self,table_name,tuple1,tuple2,m = False):
	
		if m == False:   #Mode = False is used for inserting string values
			self.sql = "insert into "+table_name+"("+",".join(tuple1)+") values"+str(tuple2)[1:-1]
			
		elif m == True:   #Mode = True is used for inserting tuple values
			self.sql = "insert into "+table_name+"("+",".join(tuple1)+") values"+str(tuple2)
			
		if self.exe():
			print("the threads has been successfully inserted!")

	'''delete thread(s)'''
	def delete(self,table_name,condition_):
		
		self.sql = "delete from "+table_name+" where "+condition_
		if self.exe():
			print("the threads has been successfully deleted!")

	'''update thread(s)'''
	def update(self,table_name,set_field,set_value,condition_):
	
		self.sql = "update "+table_name+" set "+set_field+"='"+set_value+"' where "+condition_
		if self.exe():
			print("the threads has been successfully updated!")

	'''select thread(s)'''
	def select(self):

		#put SQL statement directly at attribute, self.sql, and call exe() would be the most convenient way.
		print(self.sql)
		if self.exe(sel = True):
			print("the threads has been successfully selected!")

	'''execute SQL statements'''
	def exe(self,sel=False,alt=False):
		try:
			self.cursor.execute(self.sql)
			self.db.commit()
			self.data = self.cursor.fetchall()

			#for select() use only
			if self.data == () and sel == True:
				print("Empty set...")
			elif self.data != () and sel == True:
				for i in range(0,len(self.data)):
					print(self.data[i])
				print(str(self.cursor.rowcount)+" row(s) in set")
			return True
		except Exception as err:
			self.db.rollback()
			print("error occured :" + str(err))
			if alt == True:
				print('''\nsuggested use : object.alter(table_name, add/ drop/ change/ rename, the_rest...)\n
	alter table table_name rename new_table_name;
	alter table table_name add [Field] [TYPE] [CONSTRAINTS];
	alter table table_name drop [Field];
	alter table table_name drop foreign key [FOREIGN KEY];
	alter table table_name change [old_Field] [new_Field] [new_TYPE] [new_CONSTRAINTS];
	alter table table_name engine = [new storage engine]
	alter table toeic charset = [new charset];      #utf8 , gbk
					''')
			if sel == True:
				print('''
				select 10+20;
	select 10>20;    #result = 0     means False     #SQL has no bool but it still shows 0 or 1 instead.
	select 100>20;   #result = 1     means True
	select 10 in (10,20,30);    #result = 1    
	select 50 in (10,20,30);    #result = 0
	select 10 between 1 and 100;    #result = 1
	select 110 between 1 and 100;   #result = 0
	select id,name,classid from stu;     
	select * from stu [condition where Field = value]     
	select * from stu [condition where Field > value]
	select * from stu [condition where Field < value]
	select * from stu [condition where Field in value]    						#eg: where id in (1,3,5,7);
	select * from stu [condition where field between value1 and value2]			#where age between 20 and 25;
	select * from stu [condition where field not between value1 and value2]
	select * from stu [condition where field condition1 and condition 2]   		#where age>=20 and age<=25;
	select * from stu [condition where field condition1 or condition 2]
	select * from mydb.demo;
	select * from mydb.tt2;
	select * from mydb.tt2 where addtime is null;
	select * from stu where name like "%a%";          #fuzzy search , in middle , a
	select * from stu where name like "%ang%";		  #fuzzy search , in middle , ang
	select * from stu where name like "uu%";		  #fuzzy search , at beginning , uu
	select * from stu where name like "%uu";		  #fuzzy search , at the end , uu
	select * from stu where name like "____";         #width search , _ _ _ _ means exact 4 bytes 
	select count(id) from stu;						  #result : 11 ,  to count how many threads in column "id"

	select count(*),max(age),min(age),sum(age),avg(age) from stu;
		+----------+----------+----------+----------+----------+
		| count(*) | max(age) | min(age) | sum(age) | avg(age) |
		+----------+----------+----------+----------+----------+
		|       11 |       29 |       19 |      237 |  21.5455 |
		+----------+----------+----------+----------+----------+

	select classid from stu group by classid;         #group by field, must be the field you have selected early.
		+----------+
		| classid  |
		+----------+
		| python03 |
		| python04 |
		| python05 |
		+----------+		

	select classid,count(*) from stu group by classid;    # *******
		+----------+----------+
		| classid  | count(*) |
		+----------+----------+
		| python03 |        4 |
		| python04 |        5 |
		| python05 |        2 |
		+----------+----------+

	select classid,count(*),avg(age) from stu group by classid;
		+----------+----------+----------+
		| classid  | count(*) | avg(age) |
		+----------+----------+----------+
		| python03 |        4 |  22.7500 |
		| python04 |        5 |  21.2000 |
		| python05 |        2 |  20.0000 |
		+----------+----------+----------+

	select classid,count(*) as num from stu group by classid having num>3;   #be careful its order
		+----------+-----+
		| classid  | num |
		+----------+-----+
		| python03 |   4 |
		| python04 |   5 |
		+----------+-----+

	select classid,sex,count(*) from stu group by classid,sex;
		+----------+-----+----------+
		| classid  | sex | count(*) |
		+----------+-----+----------+
		| python03 | w   |        1 |
		| python03 | m   |        3 |
		| python04 | w   |        4 |
		| python04 | m   |        1 |
		| python05 | m   |        2 |
		+----------+-----+----------+

	select * from stu order by age [asc/desc];
	select * from stu order by classid asc,age desc;      
	select * from stu limit 5;                             #only select first 5 threads
	select * from stu order by age desc limit 3;
	select * from stu limit 2,3;					#skip the first 2 threads, and then select the following 3 threads.
	select * from stu limit 7,10;					#skip the first 7 threads, and then select the following 10 threads.
	select * from stu where classid="python03" order by age asc limit 2;

	select * from stu where age=(select max(age) from stu);  # = select * from stu order by age desc limit 
		+----+------+-----+-----+----------+
		| id | name | age | sex | classid  |
		+----+------+-----+-----+----------+
		| 10 | aa   |  29 | w   | python03 |
		+----+------+-----+-----+----------+

	select * from grade where sid=(select id from stu where name='qq01');

	select s.id,s.name,g.subject,g.score from stu s,grade g where s.id=g.sid;  
		# there are two tables, stu and grade, these are short as s and g. 
			[Fields] stu.id, stu.name, grade.subject, grade.score
			[Where] stu.id = grade.id

	select s.id,s.name,g.subject,g.score from stu s,grade g where s.id=g.sid [order by g.score desc];

	select s.id,s.name,s.classid,g.subject,g.score from stu s
	left join grade g on s.id=g.sid where s.classid="python04";   ''')

	def table_exist(self,table_name):
		self.sql = "show tables"
		self.exe()
		self.table_list = re.findall('(\'.*?\')',str(self.data))
		self.table_list = [re.sub("'",'',each) for each in self.table_list]
	
		if table_name.lower() in self.table_list:
			return True
		else:
			return False
#Main part
#to connect server
gosql = Sql_connect()

#=========================================以下為本class中的方法操作方式=============================================
#to CREATE table, write sql statement manually
gosql.sql = '''
CREATE TABLE stu (
  `Id` int(2) NOT NULL COMMENT '學號',
  `Name` varchar(10) NOT NULL COMMENT '姓名',
  `Classid` varchar(10) NOT NULL COMMENT '班級',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
'''
#gosql.create()


#to DROP table, write sql statement manually
gosql.sql = "drop table stu"
#gosql.drop()


#to ALTER table, write sql statement mostly manually
table_name = "stu"
action = "change"  
the_rest = "name name varchar(88)"
#gosql.alter(table_name,action,the_rest)


#to INSERT into table, only can insert one row at a time-> object.insert(table_name,fields,values)
table_name = "stu"
tup1 = "id","name","classid"  #tuple for fields
tup2 = '''
("1","Johnny","python09"),
("2","Monica","python06"),
("3","Joey","python06"),
("4","Reachel","python07"),
("5","Ross","python10"),
("6","Pheebe","python10"),
("7","Candler","python12")
'''  #tuple for values
#gosql.insert(table_name,tup1,tup2)


#to DELETE threads.
table_name = "stu"
where = "id = '2'"
#gosql.delete(table_name,where)


#to UPDATE set of table with condition ->object.update(table_name,set_field,set_value,condition)
table_name = "stu"
set_field = "name"
set_value = "NEW_NAME"
condition = "Examer_ID = 'GH12312341'"  #make sure the value under the condition is quoted! 
#gosql.update(table_name,set_field,set_value,condition)


#to SELECT threads, write sql statement manually
gosql.sql = '''select * from stu'''
#gosql.select()

#to fully & manually execute sql statements, write at here and call exe(True) directly.
gosql.sql = '''desc stu'''
#gosql.exe(True)
